# !/usr/bin/env python
# -*- coding: utf-8 -*-
import sys
import os
from bs4 import BeautifulSoup
from PyQt5.QtCore import pyqtSignal, QObject
import pymssql
import openpyxl
from public import gettablename, extract_float
from public import dbhelper
from public import getbrandinfo, getaccountinfo, write_log
from datetime import datetime
from PyQt5.QtGui import QTextCursor
from PyQt5.QtWidgets import QApplication, QFileDialog

from PyQt5.QtWidgets import QApplication, QTabWidget, QMessageBox, QMenu, QMenuBar, QAction

from PyQt5.QtWidgets import (QApplication, QWidget, QComboBox, QLineEdit,
                             QPushButton, QTableView, QTextEdit, QVBoxLayout,
                             QLabel, QHBoxLayout, QMessageBox, QInputDialog)
from PyQt5.QtCore import QAbstractTableModel, Qt
from PyQt5.QtWidgets import QDesktopWidget
from PyQt5.QtWidgets import QWidget, QVBoxLayout, QTableView, QProgressBar
from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel
from PyQt5.QtCore import Qt
from public import DatabaseModel
from PyQt5.QtWidgets import QApplication, QVBoxLayout, QWidget, QTableView, QTextEdit, QScrollBar
from public import (get_chrome_version, getbrandinfo, getconnection_url)
import os
from PyQt5.QtWidgets import QApplication, QWidget, QLabel
from PyQt5.QtCore import pyqtSignal, QObject
from MySQLdb import connect
import numpy as np
import pyodbc
from sqlalchemy.sql import text

import openpyxl
import pymysql
from sqlalchemy.engine.url import URL
import locale
import sys, time, random
import re
import sys
import logging
from openpyxl import load_workbook
from sqlalchemy import create_engine, false, table
import requests
import pandas
from selenium.webdriver.common.keys import Keys
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.common.by import By
from lxml import etree
import pandas as pd
from selenium.webdriver.chrome.service import Service
import socket
from PyQt5.QtWidgets import QInputDialog
from public import user_agents, generate_random_ua
from trio import current_time
from sqlalchemy.orm import sessionmaker
import shutil
import copy
from selenium.webdriver import ActionChains
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from subprocess import CREATE_NO_WINDOW
import traceback
from selenium.webdriver.common.desired_capabilities import DesiredCapabilities
from sqlalchemy import true
from public import (getbrandinfo, getaccountinfo)
from PyQt5.QtCore import QThread, pyqtSignal
import threading

desired_capabilities = DesiredCapabilities.CHROME
desired_capabilities["pageLoadStrategy"] = "none"
import winreg

user_agents = [
    "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 10.0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 6.3; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 6.3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 5.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
    "Mozilla/5.0 (Windows NT 11.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36",
]


def generate_random_ua():
    return random.choice(user_agents)


class MessageSender(QObject):
    textUpdateSignal = pyqtSignal(str)
    progressUpdateSignal = pyqtSignal(int)
    button_1UpdateSignal = pyqtSignal(bool)
    button_preUpdateSignal = pyqtSignal(bool)


class TAsin(QWidget):

    def __init__(self, userdata):
        super().__init__()
        self.notprocesssif = 0
        self.processtasktimes = 0
        self.drvier = None

        self.amazondriver = None
        self.textmemo = QTextEdit()
        self.random_ua = generate_random_ua()
        menubar = QMenuBar(self)
        file_menu = QMenu('文件', self)
        help_action = QAction('帮助', self)
        close_action = QAction('关闭', self)
        file_menu.addAction(help_action)
        file_menu.addAction(close_action)
        menubar.addMenu(file_menu)
        help_action.triggered.connect(self.showhelp)

        close_action.triggered.connect(self.closewindow)

        self.scraper_thread = None
        self.driver = None
        self.random_ua = generate_random_ua()
        # self.random_ua = random.choice(user_agents)

        self.userdata = userdata
        self.username = ''
        if 'username' in self.userdata and self.userdata['username'] != '':
            self.username = userdata["username"]

        self.accountinfo = getaccountinfo()
        account_info = self.accountinfo
        self.brandinfo = getbrandinfo()
        self.engine = create_engine(
            f"mysql+pymysql://root:Zb_200407@gz-cdb-pr3j7lhn.sql.tencentcdb.com:26590/amazon"
        )
        self.dbconn = dbhelper(server='gz-cdb-pr3j7lhn.sql.tencentcdb.com',
                               uid='root',
                               pwd='Zb_200407',
                               database='amazon',
                               port=26590,
                               creator=pymysql)

        self.connection = self.engine.connect()
        self.table_combo = QComboBox()
        self.table_combo.addItems([
            '待处理表asinlist', '处理结果表asinexisting',
            '处理完毕表asinlist_processed'
        ])
        w_width = 1280
        w_height = 720
        desktop = QDesktopWidget()
        screen_rect = desktop.screenGeometry()
        percentage = 85
        width = int(screen_rect.width() * percentage / 100)
        height = int(screen_rect.height() * percentage / 100)
        self.resize(width, height)
        current_path = os.getcwd()
        intclientuse = 1  #是否客户使用
        self.setWindowTitle(f"选品系统->当前用户:{self.username}")
        self.pai_text = QLabel(
            '如有问题请联系作者：492524133@qq.com。 温馨提示：此软件只提供学习交流 \n不得用于其他非法用途， 使用本软件所造成的一切后果\n皆有使用者自主承担'
        )
        self.entry_data = QLabel(f'系统运行的目录：{current_path}')
        #界面只保留登录界面，其他界面都是从数据库下载后执行；需要有一个版本控制系统
        """创建label_user按钮，与说明书"""
        """创建label_user关联输入"""
        self.file_lable = QLabel('处理的文件名：')
        self.entry_filepath = QLineEdit()
        self.entry_filepath.setText('预处理ASIN数据后，这里显示导入的文件名。')

        # 创建表格视图
        self.table_view = QTableView()

        self.table_view.setSelectionMode(QTableView.MultiSelection)  # 设置为多选模式
        self.table_view.setSelectionBehavior(
            QTableView.SelectRows)  # 可设置为选择整行等行为

        self.labelasin = QLabel('查询时，在这里输入asin')

        self.textasin = QLineEdit()
        # self.textasin.setText()

        self.textasin.returnPressed.connect(self.on_return_pressed)

        #         self.textasin.setFixedHeight(25)
        # # 禁用自动换行
        #         self.textasin.setLineWrapMode(QTextEdit.NoWrap)

        self.model = DatabaseModel([])
        self.table_view.setModel(self.model)
        # 创建导出按钮
        self.query_button = QPushButton('查询')
        self.query_button.clicked.connect(self.query_data)

        self.export_button = QPushButton('导出')
        self.export_button.clicked.connect(self.export_data)
        self.textmemo.append('用于处理结果：\n')

        self.button_pre = QPushButton('预处理ASIN数据')
        # self.button_pre.clicked.connect(self.impexls2table)
        self.button_pre.clicked.connect(lambda: self.parse_task(0))

        self.button_1 = QPushButton('去sif amazon处理')
        self.button_1.clicked.connect(lambda: self.parse_task(1))

        self.progress_bar = QProgressBar()
        self.sender = MessageSender()
        self.sender.textUpdateSignal.connect(self.textmemo.append)
        self.sender.progressUpdateSignal.connect(self.progress_bar.setValue)

        self.sender.button_1UpdateSignal.connect(self.button_1.setEnabled)
        self.sender.button_preUpdateSignal.connect(self.button_pre.setEnabled)

        self.button_2 = QPushButton('清除文本内容')
        self.button_2.clicked.connect(self.clearmemo)
        if self.username == None or self.username == '':

            self.textmemo.append('没有正常登录，无法使用该功能！')
            time.sleep(1)
            # self.close()
        str = f'在初次使用之前，在{current_path}安装浏览器（如果之前安装过需要卸载）.\n安装完毕后，在google浏览器输入www.amazon.com，然后选择英文\n'
        str += f'使用系统前，必须认真查看配置文件{current_path}\\account.txt\n'
        str += f'1：从卖家精灵下载的EXCEL文件\n'
        str += f'2：在程序找到文件，并且导入数据库表里asinlist\n'
        str += f'3：根据条件：读取数据库表里asinlist：是新品，有成长性，去sif,amazon查看'
        str += f'4：对asinlist处理sif,amz，数据导出，运营人员根据asinexisting进行筛选！\n'
        str += f'5：数据来源：登录某精灵->工具->选产品->导出产品，下载EXCEL->在界面预处理asin数据,然后再分析sif,amz\n'
        self.textmemo.append(str)

        for key, value in self.accountinfo.items():
            self.textmemo.append(f'配置参数：Key: {key}->=Value: {value}')
        pre_layout = QHBoxLayout()
        pre_layout1 = QHBoxLayout()

        top_layout = QHBoxLayout()
        top_layout.addWidget(self.button_pre)
        top_layout.addWidget(self.button_1)
        # top_layout.addWidget(self.button_3)
        top_layout.addWidget(self.button_2)

        pre_layout1.addWidget(self.pai_text)
        pre_layout.addWidget(self.entry_data)
        pre_layout.addWidget(self.file_lable)
        pre_layout.addWidget(self.entry_filepath)
        main_layout = QVBoxLayout()

        main_layout.addWidget(menubar)  # 前置操作
        main_layout.addLayout(pre_layout1)  # 前置操作

        main_layout.addLayout(pre_layout)  # 前置操作
        main_layout.addLayout(top_layout)

        top_layout2 = QHBoxLayout()
        top_layout2.addWidget(self.table_combo)
        top_layout2.addWidget(self.labelasin)

        top_layout2.addWidget(self.textasin)

        top_layout2.addWidget(self.query_button)
        top_layout2.addWidget(self.export_button)
        main_layout.addLayout(top_layout2)

        # main_layout.addWidget(self.progressBar)

        main_layout.addWidget(self.progress_bar)
        main_layout.addWidget(self.textmemo)
        main_layout.addWidget(self.table_view)
        userdata['action'] = 'choosegoods'
        userdata['memo'] = '登录选品界面'
        if not (self.username == None or self.username == ''):
            write_log(self.userdata['dbconnection'], self.userdata, None)
        self.setLayout(main_layout)

        self.sender = MessageSender()
        self.sender.textUpdateSignal.connect(self.textmemo.append)
        self.sender.progressUpdateSignal.connect(self.progress_bar.setValue)
        self.sender.button_1UpdateSignal.connect(self.button_1.setEnabled)
        self.sender.button_preUpdateSignal.connect(self.button_pre.setEnabled)

    def updateProgress(self, progress):
        self.progressBar.setValue(progress)

    def on_return_pressed(self):
        print("回车被按下，触发了方法。")
        self.query_data()

    def showhelp(self):
        a = 1
        sql = 'select * from us_config where key1="help"'
        dbo = self.userdata['dbconnection']
        rows = dbo.dbread_data(sql)
        self.textmemo.append(
            '\n\n<span style="color=red">请认真看下面的帮助信息哦！\n\n</span>')
        intx = 0
        for ro in rows:
            intx += 1
            styled_text = f"<span style='color:green;font-weight:bold;font-size:16pt'>{ro[1]}</span>"
            if intx % 2 == 0:
                styled_text = f"<span style='color:blue;font-weight:bold;font-size:16pt'>{ro[1]}</span>"
            self.textmemo.append(styled_text)

        self.textmemo.verticalScrollBar().setValue(
            self.textmemo.verticalScrollBar().maximum())

    def closewindow(self):
        self.close()

    def check_asinenter(self):
        cursor = self.textasin.textCursor()
        if cursor.atEnd():
            last_char = self.textasin.toPlainText()[-1]
            if last_char == '\n':
                print("回车被按下，触发了函数。")
                self.query_data()

    def query_data(self):
        table_name = gettablename(self.table_combo.currentText()).lower()
        asin = self.textasin.text()
        # self.textasin.setText("")
        query = ''
        # print(f"原始：{self.table_combo.currentText()}，英文部分：{table_name}")

        query = f"SELECT * FROM {table_name} WHERE username='{self.username}' and asin like  '%{asin}%' "
        if asin == '' or len(asin) == 0:
            query = f"SELECT * FROM {table_name} WHERE username='{self.username}'   "
        conn = None
        try:
            conn = self.dbconn
            # self.connect_to_database()
            # cursor = conn.cursor()
            # print(query)
            conn = self.dbconn.get_connection()
            cursor = conn.cursor()
            cursor.execute(query)
            data = cursor.fetchall()
            column_names = [column[0] for column in cursor.description]
            self.model = DatabaseModel(data)
            self.model.setcolumns(column_names)
            self.table_view.setModel(self.model)
            # 设置列名显示
            headers = [
                self.model.headerData(i, Qt.Horizontal)
                for i in range(self.model.columnCount())
            ]
            for col, header in enumerate(headers):
                self.model.setHeaderData(col, Qt.Horizontal, header)

            strline = f"查询表 {table_name}，asin：{asin},记录数量：{len(data)}"
            self.textmemo.append(strline)
            return result
        except pymssql.Error as e:
            # print(e)
            QMessageBox.critical(self, 'Error', f"Query failed: {e}")
        finally:
            self.userdata['action'] = 'choosegoods:query_data'
            self.userdata['memo'] = asin
            write_log(self.userdata['dbconnection'], self.userdata, None)
            self.dbconn.close_connection(conn)
            return query

    def scroll_to_random_position(self, driver):
        # 获取页面的高度
        total_height = driver.execute_script(
            "return document.body.scrollHeight")

        # 生成随机滚动位置
        random_position = random.randint(int(total_height * 0.7), total_height)

        # 滚动到随机位置
        driver.execute_script(f"window.scrollTo(0, {random_position});")

    def parse_task(self, param):

        self.processtasktimes += 1
        if not 'username' in self.userdata or self.userdata['username'] == '':
            self.sender.textUpdateSignal.emit(f"请尽快登录哦")

        if self.processtasktimes > 5 and (not 'username' in self.userdata
                                          and self.userdata['username'] == ''):
            self.sender.textUpdateSignal.emit(
                f"已经执行{self.processtasktimes}次，请去登录或者注册后登录")
            return
        self.textmemo.verticalScrollBar().setValue(
            self.textmemo.verticalScrollBar().maximum())
        if not self.scraper_thread or not self.scraper_thread.is_alive():
            if param == 0:
                file_paths, _ = QFileDialog.getOpenFileNames(
                    None, "请选择文件(可以同时选择多个文件)", "", "Excel 文件 (*.xls *.xlsx)")
                self.scraper_thread = threading.Thread(
                    target=self.impAsins2List, args=(file_paths, ))
            if param == 1:
                account_info = getaccountinfo()
                acctxt = account_info
                file1 = f'{os.getcwd()}\\account.txt'
                intuseself = 1  #0代表给客户使用，1表示自己使用
                # 设置日志记录器
                now = datetime.datetime.now()
                current_time = now.strftime("%Y-%m-%d %H:%M:%S")
                for k in range(5):
                    logging.info('')

                logging.info(
                    f'开始记录日志时间：{current_time}，技术支持：492524133@qq.com,加vx:yiliyamei注明sif分析.谢谢'
                )
                # headers = {
                #     'accept':
                #     'application/json, text/plain, */*',
                #     'accept-encoding':
                #     'gzip, deflate, br, zstd',
                #     'accept-language':
                #     'zh-CN,zh;q=0.9',
                #     'cache-control':
                #     'no-cache',
                #     'connection':
                #     'keep-alive',
                #     'content-length':
                #     '46',
                #     'content-type':
                #     'application/json;charset=UTF-8',
                #     'cookie':
                #     '_clck=1ihaqt6%7C2%7Cfo1%7C0%7C1677; _clsk=1fthdot%7C1722747306574%7C11%7C1%7Cs.clarity.ms%2Fcollect',
                #     'host':
                #     'www.sif.com',
                #     'origin':
                #     'https://www.sif.com',
                #     'pragma':
                #     'no-cache',
                #     'referer':
                #     'https://www.sif.com/',
                #     'sec-ch-ua':
                #     '"Not)A;Brand";v="99", "Google Chrome";v="127", "Chromium";v="127"',
                #     'sec-ch-ua-mobile':
                #     '?0',
                #     'sec-ch-ua-platform':
                #     'Windows',
                #     'sec-fetch-dest':
                #     'empty',
                #     'sec-fetch-mode':
                #     'cors',
                #     'sec-fetch-site':
                #     'same-origin',
                #     'user-agent':
                #     'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36',
                # }
                headers = {"User-Agent": self.random_ua}
                ver1 = get_chrome_version()
                if intuseself == 1:
                    self.sender.textUpdateSignal.emit(f'GOOGLE浏览器版本{ver1}文件\n')
                try:
                    chrome_options = Options()
                    chrome_options.add_argument('--disable-dev-shm-usage')
                    # 创建Chrome浏览器实例
                    if intuseself == 0:
                        chrome_options.add_argument("--headless")  # 添加无头模式参数
                    chrome_options.add_argument("--no-sandbox")
                    chrome_options.add_experimental_option(
                        'excludeSwitches', ['enable-automation'])
                    chrome_options.add_argument('--ignore-certificate-errors')
                    chrome_options.add_argument('--ignore-ssl-errors')

                    # 创建一个webdriver实例
                    chrome_options.add_argument('--disable-gpu-vsync')
                    current_path = os.getcwd()
                    # 设置代理
                    #proxy2=ipdatalist[random.randint(0,len(ipdatalist)-1)]
                    #chrome_options.add_argument(f'--proxy-server={proxy2}')
                    chromedriver_path = os.path.join(current_path,
                                                     "chromedriver.exe")
                    logging.info(f'chromedriver_path={chromedriver_path}')
                    chrome_options.add_argument(
                        f"--user-agent={headers['User-Agent']}")
                    # 创建 Service 对象并传入路径
                    service = Service(executable_path=chromedriver_path)

                    # 创建 WebDriver 实例时传入 Service 对象和 ChromeOptions

                    if self.notprocesssif == 0:
                        self.driver = webdriver.Chrome(service=service,
                                                       options=chrome_options)
                    self.amazondriver = webdriver.Chrome(
                        service=service, options=chrome_options)
                    ver2 = self.driver.capabilities['chrome'][
                        'chromedriverVersion'].split(' ')[0]
                    if intuseself == 1:
                        self.textmemo.append(f'chromedriver版本{ver2}\n')
                    logging.info(f'chromedriver版本{ver2}\n')
                    #driver = webdriver.Chrome()
                    #driver = webdriver.Chrome(options=chrome_options)
                    self.driver.implicitly_wait(3)
                    self.amazondriver.set_page_load_timeout(
                        int(account_info['sifsleep']))
                    self.driver.set_page_load_timeout(
                        int(account_info['amazonsleep']))

                    self.amazondriver.get("https://www.amazon.com")
                    if self.notprocesssif == 0:
                        self.driver.get("https://www.sif.com/login")

                        # 将垂直滚动条拉到最低
                        self.driver.execute_script(
                            "window.scrollTo(0, document.body.scrollHeight);")
                        time.sleep(random.random() / 4)
                        # 将平行滚动条拉到最右侧
                        self.driver.execute_script(
                            "window.scrollTo(document.body.scrollWidth, 0);")
                except Exception as e:
                    print(e)
                msg_box = QMessageBox()
                msg_box.setIcon(QMessageBox.Information)
                msg_box.setText(
                    f"如果你认真阅读文件，并且填写好文件{file1}，登录相关网站后，请确认，确认后程序继续执行！")
                msg_box.setWindowTitle("客户自己访问相关网站登录账号")
                msg_box.exec_()

                self.scraper_thread = threading.Thread(
                    target=self.parse_next_url)
            print("Before starting  thread.")

            self.scraper_thread.start()
            print("After starting thread.")

    def parse_next_url(self):
        self.crawlamz()
        #parseip(self)
        driver = self.driver
        amazondriver = self.amazondriver
        intuseself = 1  #0代表给客户使用，1表示自己使用
        # 设置日志记录器
        from sqlalchemy.sql import text
        now = datetime.datetime.now() 
        current_time = now.strftime("%Y-%m-%d %H:%M:%S")
        for k in range(5):
            logging.info('')

        logging.info(
            f'开始记录日志时间：{current_time}，技术支持：492524133@qq.com,加vx:yiliyamei注明sif分析.谢谢'
        )

        current_path = os.getcwd()
        account_info = getaccountinfo()
        engine = self.engine
        strmonthsale = f' '
        # sql=text('delete from us_temptable')
        # result=None
        # with self.engine.connect() as connection:
        #     result = connection.execute(sql)
        sql = text("select distinct * from asinlist where 1=1 ")
        # print(sql)
        # sql2=text(sql)
        result = None
        record_count = 0
        columns = []
        connection = self.engine.connect()
        result = connection.execute(sql)
        columns = [col[0] for col in result.cursor.description]
        all_results = result.fetchall()
        record_count = len(all_results)

        asinstore_index = columns.index('卖家首页')
        asinreview_index = columns.index('评分')
        ver1 = get_chrome_version()
        dictsave = {}  #保存处理后的数据字典
        asindata = []
        time.sleep(1)
        # 使用XPath表达式定位元素
        #页面进入查流量结构
        element = driver.find_element(By.XPATH, "//span[text()='查流量结构']")
        element.click()
        # 等待登录成功
        # 获取列名为'asin'和'卖家首页'的列
        #只执行10行
        intx = 0
        column_a_value = ''
        #asin,asin店铺首页对应的列序号
        #for index, row in df.iloc[1:].iterrows():
        storecolname = ''
        index = 0
        actions = ActionChains(driver)
        dictsaves = []
        fweight = 400.0
        # for row in result:

        for row in all_results:
            if self.notprocesssif == 1:
                break

            dictsave = {}
            asin = row[0]
            # print(f'访问sif.com,第{index+1}行:{asin}')
            index += 1
            column_ar_value = row[asinstore_index]
            reviews = row[asinreview_index]
            asindata.append({'asin': asin})
            if reviews != 'nan' and not reviews is None:
                if float(reviews) < float(account_info['colrate']):
                    # print (f"{asin}评分低于预设{account_info['colrate']}，此行信息不处理")
                    continue

            intx = intx + 1
            if intx > int(account_info['maxdownloadsifasin']):
                break
            # column_a_value = row.iloc[intasincol]  # 注意索引从 0 开始，所以需要减 1
            column_a_value = asin
            column_ar_value = 'nan'
            column_ar_value = row[asinstore_index]
            self.sender.textUpdateSignal.emit(
                f'当前行 {index },asin：{column_a_value},当前店铺首页:{column_ar_value}\n'
            )

            logging.info(
                f'{current_time},Row {index }:  Column a  value is {column_a_value}, Column ar value is {column_ar_value}'
            )
            try:
                time.sleep(2 * random.random())
                elements = driver.find_elements(
                    By.XPATH, "//input[@placeholder='输入ASIN，多个逗号隔开']")
                counts_a = len(elements)
                print(elements)
                input_element = elements[0]

                print(f'asin element->:{input_element}')

                for j in range(15):
                    input_element.send_keys(Keys.BACKSPACE)
                    time.sleep(random.random() * 0.05)
                input_element.send_keys(column_a_value)
                # 模拟按下回车键
                time.sleep(random.random() * 0.15)
                input_element.send_keys(Keys.ENTER)
                chart_content_element = None
                time.sleep(random.random() * 0.15)

                wait = WebDriverWait(driver, 10)  # 设置最长等待时间为 10 秒
                time.sleep(
                    int(account_info['sifsleep']) / 20 + random.random() / 100)
                try:
                    chart_content_element = wait.until(
                        EC.presence_of_element_located(
                            (By.CSS_SELECTOR, 'div.chart_content')))
                    # element = driver.find_element(By.XPATH, '//span[contains(text(), "下载图表")]')
                    actions.move_to_element(chart_content_element).perform()
                except Exception as e:
                    chart_content_element = wait.until(
                        EC.presence_of_element_located(
                            (By.CSS_SELECTOR, 'div.chart_content')))
                    actions.move_to_element(chart_content_element).perform()
                finally:
                    soup = BeautifulSoup(
                        chart_content_element.get_attribute("outerHTML"),
                        'html.parser')
                    titles = [
                        span['title']
                        for span in soup.find_all('span', {'title': True})
                    ]
                    ftraffic_org_num = 0.0
                    ftraffic_ad_num = 0.0
                    ftraffic_guide_num = 0.0
                    for indexj, valuej in enumerate(titles):
                        print(f"下标是 {indexj}，值是 {valuej}")
                        if valuej == '自然流量':
                            ftraffic_org_num = titles[3 + indexj].split(
                                ':')[1].replace(',', '').replace('占比', '')
                        if valuej == '广告流量':
                            ftraffic_ad_num = titles[3 + indexj].split(
                                ':')[1].replace(',', '').replace('占比', '')
                        if valuej == '推荐流量':
                            ftraffic_guide_num = titles[3 + indexj].split(
                                ':')[1].replace(',', '').replace('占比', '')
                    irate = 9999.99
                    if float(ftraffic_ad_num) > 0.0:
                        irate = float(
                            float(ftraffic_org_num) +
                            float(ftraffic_guide_num)) / float(ftraffic_ad_num)
                    self.sender.textUpdateSignal.emit(
                        f"查询流量结构，asin：{column_a_value}完毕")
                    self.sender.progressUpdateSignal.emit(
                        int(index * 100 / record_count))
                    sql = f""" insert into asinexisting(
                        asin,父asin  ,自然流量 ,广告流量 ,推荐流量  ,自然流量与广告流量比值,asin评分满分5 ,username  ,opdate ,ratingcount,asinstatus
                          ) select asin,父asin,{ftraffic_org_num},{ftraffic_ad_num},{ftraffic_guide_num},{irate},评分,
                          username, {self.userdata['datefunction']}  ,评分数,'lackamazon' from asinlist where asin='{asin}'
                          
                        """
                    dictsave = {
                        'asin': asin,
                        'org': ftraffic_org_num,
                        'ad': ftraffic_ad_num,
                        'guide': ftraffic_guide_num,
                        'rate': irate
                    }
                    try:
                        with engine.connect() as connection:
                            print(sql)
                            connection.execute(text(sql))

                            connection.commit()
                        self.sender.textUpdateSignal.emit(
                            f"第{index+1}行,asin：{dictsave}数据插入数据库正确")
                    except Exception as e:
                        self.sender.textUpdateSignal.emit(
                            f"数据库插入错误: {asin},错误信息{e}")
                # 现在可以使用 BeautifulSoup 的方法来处理这个元素
            except Exception as e:
                print(e)
                logging.info(f'访问sif出现错误：{e}\n')

        self.sender.textUpdateSignal.emit(f"asin数据从sif.com处理完毕")
        self.crawlamz()

    def crawlamz(self):
        urlx = [
            "https://www.sif.com/", "https://www.ku6.com",
            "https://www.sogou.com"
        ]
        urlp = urlx[random.randint(0, 2)]
        dictsave = {}  #保存处理后的数据字典
        account_info = getaccountinfo()
        engine = self.engine
        sql = text(
            "select distinct asin,asin评分满分5,自然流量与广告流量比值,mainpage from asinexisting where asinstatus='lackamazon'  "
        )
        # print(sql)
        # sql2=text(sql)
        resultamazon = None
        record_count = 0
        connection = self.engine.connect()
        resultamazon = connection.execute(sql)
        all_results = resultamazon.fetchall()
        record_count = len(all_results)
        #time.sleep(random.random())
        # driver.get("https://www.amazon.com/")

        # time.sleep(random.random() / 4)
        # amazondriver.execute_script(
        #     "window.scrollTo(0, document.body.scrollHeight);")

        # time.sleep(1)
        intuseself = 1
        intx = 0
        dictsaves = []
        column_a_value = ''
        column_ar_value = ''
        columnaddview = 0
        current_path = os.getcwd()

        logging.info(f'开始处理amz数据：{current_path}\n')

        index = 0
        # asin评分满分5
        for row in all_results:
            index += 1
            dictsave = {}
            # column_a_value = row.iloc[intasincol]  # 注意索引从 0 开始，所以需要减 1
            column_a_value = row[0]
            asin = column_a_value
            reviews = row[1]
            irate = row[2]
            column_ar_value = row[3]
            asin = column_a_value.lower()
            self.sender.textUpdateSignal.emit(
                f'处理amz数据，第{index}行数据为：{asin}->{reviews}->{column_ar_value}')
            print(
                f'处理amz数据，第数据，第{index}行数据为：{asin}->{reviews}->{column_ar_value}'
            )
            if reviews != 'nan' and not reviews is None:
                if float(reviews) < float(account_info['colrate']):
                    print(f"{asin}评分低于预设{account_info['colrate']}，此行信息不处理")
                    continue
            intx = intx + 1

            found_files = ''
            intfilecount = 0
            lastfilename = ''
            amazondriver = self.amazondriver
            if irate > 2.0:
                iok = '1'  #还要看店铺评级是否可以
                #去亚马逊店铺看看
                urlstore = column_ar_value
                dictsave["mainpage"] = column_ar_value
                text3 = ''
                try:
                    if column_ar_value is None or not (
                            type(column_ar_value) == str or
                            not "www.amazon.com" in column_ar_value.lower()):
                        #此刻通过ASIN页面，去找首页
                        urla = f'https://www.amazon.com/dp/{column_a_value}?th=1'
                        self.sender.textUpdateSignal.emit(f'准备访问第{urla}')
                        amazondriver.get(urla)
                        amazondriver.implicitly_wait(
                            int(account_info['amazonsleep']) - 0.5 +
                            random.random())
                        wait2 = WebDriverWait(
                            amazondriver,
                            int(account_info['amazonsleep']) - 0.5 +
                            random.random())
                        xelement1 = wait2.until(
                            EC.presence_of_element_located(
                                (By.CSS_SELECTOR,
                                 '#bylineInfo.a-link-normal')))

                        href_value = ''
                        if xelement1:

                            href_value = xelement1.get_attribute('href')
                            print(href_value)
                        else:
                            print("No elements found.")
                        #获取店铺地址
                        # time.sleep(random.random() * 4)  #大约等待1秒
                        urlstore = xelement1.get_attribute('href')
                        dictsave["mainpage"] = urlstore
                        self.sender.textUpdateSignal.emit(f'准备访问第{urlstore}')

                    amazondriver.get(urlstore)
                    wait2 = WebDriverWait(
                        amazondriver,
                        int(account_info['amazonsleep']) - 0.5 +
                        random.random())
                    # time.sleep(int(account_info['amazonsleep']) -0.5+ random.random())
                    element3 = amazondriver.find_element(
                        By.XPATH,
                        "//a[contains(@class, 'a-link-normal feedback-detail-description no-text-decoration')]"
                    )

                    # 获取元素的文本内容
                    text3 = element3.text
                    time.sleep(
                        int(account_info['amazonsleep']) - 0.5 +
                        random.random())
                    #对上述进行分析
                except Exception as e:
                    print(e)
                number_rategood = 0  #满分100分
                percent_pattern = r'(\d+)%'
                percent_match = re.search(percent_pattern, text3)
                if percent_match:
                    number_rategood = int(percent_match.group(1))

                # 获取 54 '12'
                number_totalreviews = 0
                count_pattern = r'(\d+) ratings'  #(1439 ratings) '99% 好评，在过去 12 个月中（共 1149 条评价）'
                count_match = re.search(count_pattern, text3)
                if count_match:
                    number_totalreviews = int(count_match.group(1))
                #读取评价数
                else:
                    count_pattern = r'(\d+) 条'  #(1439 ratings) '99% 好评，在过去 12 个月中（共 1149 条评价）'
                    count_match = re.search(count_pattern, text3)
                    if count_match:
                        number_totalreviews = int(count_match.group(1))
                if intuseself == 1:
                    dictsave['好评率满分100'] = number_rategood
                if intuseself == 1:
                    dictsave['12月累计评价'] = number_totalreviews
                irates = 0
                print(
                    f'number_rategood={number_rategood}->number_totalreviews={number_totalreviews}'
                )
                sql = f' update asinexisting set asinstatus="ok" ,好评率满分100={number_rategood},12月累计评价={number_totalreviews} where asin="{column_a_value}"'
                try:
                    with engine.connect() as connection:
                        print(sql)
                        connection.execute(text(sql))
                        connection.commit()
                        sql = f""" INSERT INTO asinlist_processed SELECT * FROM asinlist where asin='{asin}' limit 1"""
                        print(sql)
                        connection.execute(text(sql))
                        connection.commit()
                        sql = f" delete  FROM asinlist where asin='{asin}' "
                        print(sql)
                        connection.execute(text(sql))
                        connection.commit()
                    self.sender.textUpdateSignal.emit(
                        f"第{index+1}行,asin：{column_a_value}数据插入数据库正确")
                except Exception as e:
                    self.sender.textUpdateSignal.emit(
                        f'处理asin:{column_a_value},从amazon.com下载:{column_ar_value}后的文件出现错误：{e}\n'
                    )
                    logging.info(
                        f'处理asin:{column_a_value},从amazon.com下载:{column_ar_value}后的文件出现错误：{e}\n'
                    )
            # except Exception as e:
            #     self.sender.textUpdateSignal.emit(
            #         f'处理asin:{column_a_value},从amazon.com下载:{column_ar_value}后的文件出现错误：{e}\n'
            #     )
            #     logging.info(
            #         f'处理asin:{column_a_value},从amazon.com下载:{column_ar_value}后的文件出现错误：{e}\n'
            #     )

        self.userdata['action'] = 'sif amz task'
        self.userdata['memo'] = 'threading'
        write_log(self.userdata['dbconnection'], self.userdata, None)

    def impAsins2List(self, file_paths):
        # 读取Excel文件
        account_info = self.accountinfo
        infiles = 0
        row_index = 0
        spec = [
            '<', '>', '$', '*', '&', '%', '.', '"', '+', '-', '@', '"', '\\',
            '//', '##'
        ]
        spec.append('(')
        spec.append(')')
        spec.append(' ')
        spec.append("'")
        spec.append('"')
        spec.append('#')
        spec.append('.')
        introws = 2
        intimpdebug = 0
        engine = self.engine
        dbo = dbhelper()
        Session = sessionmaker(bind=engine)
        session = Session()
        insertappends = []
        index1 = 0

        index1 += 1
        for file in file_paths:
            file_lower = file.lower()
            if (file_lower.endswith('.xlsx') or file_lower.endswith('.xls')):
                file_path = file
                excel_file = file_path
                workbook = None
                try:
                    workbook = openpyxl.load_workbook(excel_file)
                except Exception as e:
                    print(e)
                    continue
                # 选择要读取的工作表
                sheet = workbook.active
                first_row = sheet[1]
                intgetcolumn = 1
                intinvalidcolumns = 0
                #找到列名了
                column_names = []
                for cell in first_row:
                    value = cell.value
                    if not value or value == '':
                        intinvalidcolumns += 1
                    if intinvalidcolumns > 3:
                        intgetcolumn = 0
                        break
                    if value is not None:
                        column_names.append(value)
                    else:
                        column_names.append("")
                if intgetcolumn == 0:
                    first_row = sheet[2]
                    column_names = []
                    for cell in first_row:
                        value = cell.value
                        if value is not None:
                            column_names.append(value)
                        else:
                            column_names.append("")

                intxx = 0
                for col in column_names:
                    for inst in spec:
                        column_names[intxx] = column_names[intxx].replace(
                            inst, '')
                    intxx += 1
                print(column_names)

                dataappends = []
                intcol = 0
                row_index = 0
                #从第二行开始读，还是第一行开始读，从startrow开始读；
                startrow = 3 - intgetcolumn
                for row_index in range(startrow, sheet.max_row + 1):
                    values = []
                    intcol = 0
                    newcols = []  #column_names[:]
                    row = sheet[row_index]
                    intweight = 0
                    try:
                        insertcolumns = ''
                        for col_index in range(1, len(column_names)):

                            column_name = column_names[col_index - 1]
                            if '重量' in column_name:
                                intweight += 1
                            if intweight > 1 and '重量' in column_name:
                                continue
                            if not row[col_index] or pd.isna(row[intcol]):
                                # del newcols[col_index-1]
                                continue
                            value = sheet.cell(row=row_index,
                                               column=col_index).value
                            if value is None or (isinstance(value, str) and
                                                 (value.strip() == ''
                                                  or len(value) > 200)):
                                # del newcols[col_index-1]
                                continue
                            intcol += 1
                            # insertcolumns = insertcolumns + colsn[
                            #     col_index - 1] + ','
                            # values[f'column{intcol}'] = value
                            values.append(f"'{value}'")
                            newcols.append(column_names[col_index - 1])
                            print(
                                f"Row {row_index}, Column {col_index}: {value}"
                            )
                        self.sender.textUpdateSignal.emit(
                            f"Row {row_index},value:{row}")
                        print('loop end')
                        columns = ', '.join(newcols)
                        valuecolumns = ', '.join(values)
                        insert_query = f"""INSERT INTO asinlist ({columns},opdate,username) VALUES 
                            ({valuecolumns},{self.userdata["datefunction"]},'{self.userdata["username"]}')"""
                        intpro = int(row_index * 100 /
                                     (sheet.max_row - startrow))
                        if intpro > 100:
                            intpro = 100
                        self.sender.progressUpdateSignal.emit(intpro)
                        print(insert_query)
                        try:
                            # dbo.dbexecutemany(insert_query, values)
                            with engine.connect() as connection:
                                connection.execute(text(insert_query))
                                connection.commit()
                        except Exception as e:
                            self.sender.textUpdateSignal.emit(
                                f"导入数据库失败！行:{row_index}:文件夹{account_info['asindirpre']}:{e}{insert_query} "
                            )
                            logging.info(
                                f"导入数据库失败！行:{row_index}:文件夹{account_info['asindirpre']}:{e}{insert_query} "
                            )
                    except Exception as e:
                        self.sender.textUpdateSignal.emit(
                            f"导入数据库失败！行:{row_index}:文件夹{account_info['asindirpre']}:{e} "
                        )
                        logging.info(
                            f"导入数据库失败！行:{row_index}:文件夹{account_info['asindirpre']}:{e} "
                        )

                workbook.close()
                # os.remove(excel_file)
                print(excel_file)
        self.userdata['action'] = 'impintoasin'
        self.userdata['memo'] = 'exceltodatabase'
        write_log(self.userdata['dbconnection'], self.userdata, None)

    def clearmemo(self):
        """定义触发事件2，删除文本框中内容"""
        self.textmemo.setText("")
        self.userdata['action'] = 'choosegoods:cleartext'
        self.userdata['memo'] = '清除文本'
        if self.userdata['username']:
            write_log(self.userdata['dbconnection'], self.userdata, None)

    def center(self):
        """创建窗口居中函数方法"""
        ws = self.window.winfo_screenwidth()
        hs = self.window.winfo_screenheight()
        x = int((ws / 2) - (800 / 2))
        y = int((hs / 2) - (700 / 2))
        self.window.geometry('{}x{}+{}+{}'.format(800, 700, x, y))

    def run_loop(self):
        """禁止修改窗体大小规格"""
        self.window.resizable(False, False)
        """窗口居中"""
        self.center()
        """窗口维持--持久化"""
        self.window.mainloop()

    def export_data(self):
        current_path = os.getcwd()
        try:
            conn = self.dbconn.get_connection()
            cursor = conn.cursor()
            cursor.execute(self.query_data())

            data = cursor.fetchall()
            column_names = [column[0] for column in cursor.description]
            #

            wb = openpyxl.Workbook()
            ws = wb.active
            ws.append(column_names)
            for row in data:
                ws.append(row)

            formatted_datetime = f'{random.randint(0,10)}{random.randint(0,10)}{random.randint(0,10)}'
            
            table_name = self.table_combo.currentText().lower().strip()

            filename = os.path.join(current_path,
                                    f'{table_name}{formatted_datetime}.xlsx').strip()
            wb.save(filename)
            str = f'<span style="color:red;">导出文件：{filename}</span>'
            self.sender.textUpdateSignal.emit(str)
        except Exception as e:  #except pymssql.Error as e:
            QMessageBox.critical(self, 'Error', f"Export failed: {e}")
        finally:
            wb.close()
            self.dbconn.close_connection(conn)

    def Save_Data(self, price, bianhao, url, page, number, driver):
        pwd = os.path.dirname(sys.executable)
        if getattr(sys, 'frozen', False):
            pwd = os.path.dirname(sys.executable)
        else:
            pwd = os.path.abspath(".")

        data = f'{url}-----{bianhao}-----{price}\n'
        if page == 101:
            self.textmemo.append('此次采集结束\n')
            driver.quit()
            self.window.quit()
        else:
            if not os.path.exists(os.path.join(pwd, '天猫商品')):
                os.mkdir(os.path.join(pwd, '天猫商品'))
            with open(os.path.join(os.path.join(pwd, '天猫商品'),
                                   f'{self.guan}.txt'),
                      'a+',
                      encoding='utf-8') as f:
                f.write(data)
            self.textmemo.append(f'第{page}页-第{number}件商品数据下载完成\n')


# 资源文件目录访问
def source_path(relative_path):
    if getattr(sys, 'frozen', False):
        base_path = sys._MEIPASS
    else:
        base_path = os.path.abspath(".")
    return os.path.join(base_path, relative_path)


# 修改当前工作目录，使得资源文件可以被正确访问

if __name__ == '__main__':
    try:
        cd = source_path('')
        os.chdir(cd)

        app = QApplication(sys.argv)
        engine = create_engine(
            f"mysql+pymysql://root:Zb_200407@gz-cdb-pr3j7lhn.sql.tencentcdb.com:26590/amazon"
        )
        dbconn = dbhelper(server='gz-cdb-pr3j7lhn.sql.tencentcdb.com',
                          uid='root',
                          pwd='Zb_200407',
                          database='amazon',
                          port=26590,
                          creator=pymysql)

        connection = engine.connect()
        accountinfo = getaccountinfo()
        databasetype = accountinfo['databasetype']
        dbconn = None

        if databasetype.lower() != 'mysql':
            print('a23c')
            dbconn = dbhelper()
            print('a23d')
            datefunction = 'getdate()'
        else:
            datefunction = 'now()'
            print('a23')
            dbconn = dbhelper(server='gz-cdb-pr3j7lhn.sql.tencentcdb.com',
                              uid='root',
                              pwd='Zb_200407',
                              database='amazon',
                              port=26590,
                              creator=pymysql)
        userdata = {
            'gateway': '',
            'ip': 'self.ip',
            'username': 'q11',
            'disknumber': 'self.disknumber',
            'dbconnection': dbconn,
            'databasetype': databasetype,
            'datefunction': 'now()',
            'debug': 1
        }
        # userdata = {
        #     'gateway': '',
        #     'ip': 'self.ip',
        #     'disknumber': 'self.disknumber',
        #     'dbconnection': dbconn,
        #     'databasetype': databasetype,
        #     'datefunction': 'now()',
        #     'debug': 1
        # }
        ts = TAsin(userdata)
        ts.show()
        app.exec_()
    except Exception as e:
        print(e)
